﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using StockManagement.Model;
using System.Data.SqlClient;
using System.Data;
using StockManagement.ViewModels;

namespace StockManagement.Helper
{
    public delegate bool ProductUpdatedDelegate(ProductViewModel product);
    public delegate void MaterialDeletedDelegate(Guid materialID);
    public delegate void MaterialUpdatedDelegate();
    public delegate void ProductDeletedDelegate(Guid productID);
    public delegate void ProdUpdatedDelegate();
    public delegate void ProductAddedDelegate();

    public class DataHelper
    {
        public static IEnumerable<MaterialViewModel> GetMaterialsList()
        {
            List<MaterialViewModel> _materialCollection = new List<MaterialViewModel>();
            string query = @"SELECT MaterialID, Name, MaterialType, MaterialCode, VendorID,Rate,Quantity, Date,Vat,Purchaser FROM Material";

            // Fill the Set with the data
            using (SqlConnection conn = DatabaseHelper.GetConnection())
            {
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = query;

                    DataSet ds = new DataSet("Material");                    
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    foreach (DataRow item in ds.Tables[0].Rows)
                    {
                        //var mat = new Material(item);
                        _materialCollection.Add(new MaterialViewModel(item));
                    }
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }
            }

            return _materialCollection;
        }

        private static ObservableCollection<MaterialViewModel> LoadMaterialStock()
        {
            ObservableCollection<MaterialViewModel> _materialCollection = new ObservableCollection<MaterialViewModel>();
            for (int index = 1; index < 6; index++)
            {
                _materialCollection.Add(new MaterialViewModel()
                {
                    MaterialCode = string.Format("MatCode {0}", index),
                    MaterialType = Helper.EnumMaterialType.Trading,
                    MaterialName = string.Format("MatName {0}", index),
                    MaterialRate = index * 5
                    //VendorID = string.Format("MatVendor {0}", index)
                });
            }

            for (int index = 6; index < 11; index++)
            {
                _materialCollection.Add(new MaterialViewModel()
                {
                    MaterialCode = string.Format("MatCode {0}", index),
                    MaterialType = Helper.EnumMaterialType.Manufacturing,
                    MaterialName = string.Format("MatName {0}", index),
                    MaterialRate = index * 5
                    //VendorID = string.Format("MatVendor {0}", index)
                });
            }

            for (int index = 11; index < 16; index++)
            {
                _materialCollection.Add(new MaterialViewModel()
                {
                    MaterialCode = string.Format("MatCode {0}", index),
                    MaterialType = Helper.EnumMaterialType.Trading,
                    MaterialName = string.Format("MatName {0}", index),
                    MaterialRate = index * 5
                    //VendorID = string.Format("MatVendor {0}", index)
                });
            }

            return _materialCollection;

        }

        public static IEnumerable<VendorViewModel> GetVendorList()
        {
            List<VendorViewModel> _vendorCollection = new List<VendorViewModel>();
            string query = @"SELECT [VendorID],[FirstName],[LastName] FROM [Vendor]";
            
            // Fill the Set with the data
            using (SqlConnection conn = DatabaseHelper.GetConnection())
            {
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = query;

                    DataSet ds = new DataSet("Vendor");
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    foreach (DataRow item in ds.Tables[0].Rows)
                    {
                        var vendor = new VendorViewModel(item);
                        if(!vendor.VendorID.Equals(Guid.Empty))
                            _vendorCollection.Add(vendor);
                    }
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }
            }

            return _vendorCollection;
        }

        public static IEnumerable<ProductViewModel> GetProductList()
        {
            List<ProductViewModel> productCollection = new List<ProductViewModel>();
            string query = @"SELECT [ProductID],[ProductName],[ProductCode],[Type],[Status],[Date],[Purchaser] FROM [Product]";
            //            string query2 = @"SELECT pd.[MaterialID],pd.[Quantity],mat.Name,mat.MaterialCode FROM [ProductDetails] as pd, Material as mat
            //WHERE pd.ProductID = '++' and pd.[MaterialID] = mat.MaterialID";
            // Fill the Set with the data
            using (SqlConnection conn = DatabaseHelper.GetConnection())
            {
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = query;

                    DataSet ds = new DataSet("Product");
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    ds.Tables.Clear();
                    ds.Tables.Add("Product");
                    ds.Tables.Add("ProductMaterials");
                    da.Fill(ds.Tables["Product"]);
                    foreach (DataRow item in ds.Tables["Product"].Rows)
                    {
                        var prod = new Product(item);
                        var productID = item["ProductID"].ToString();
                        string query2 = @"SELECT pd.[MaterialID],pd.[Quantity],mat.Name,mat.MaterialCode FROM [ProductDetails] as pd, Material as mat
                        WHERE pd.ProductID = '" + productID + "' and pd.[MaterialID] = mat.MaterialID";
                        cmd.CommandText = query2;
                        da.Fill(ds.Tables["ProductMaterials"]);
                        foreach (DataRow prodmat in ds.Tables["ProductMaterials"].Rows)
                        {
                            prod.MaterialsQuantity.Add(new ProductMaterial(prodmat));

                        }
                        productCollection.Add(new ProductViewModel(prod));
                    }
                    //foreach (DataRow item in ds.Tables["Product"].Rows)
                    //{
                    //    var mat = new Product(item);

                    //}
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }
            }

            return productCollection;
            //for (int index = 1; index < 6; index++)
            //{
            //    productCollection.Add(new ProductViewModel()
            //    {
            //        ProductCode = string.Format("ProdCode {0}", index),
            //        ProductType = Helper.EnumMaterialType.Trading,
            //        ProductName = string.Format("ProdName {0}", index),
            //        MaterialsQuantity = new ProductMaterialList()
            //    });
            //}

            //for (int index = 6; index < 9; index++)
            //{
            //    productCollection.Add(new ProductViewModel()
            //    {
            //        ProductCode = string.Format("ProdCode {0}", index),
            //        ProductType = Helper.EnumMaterialType.Manufacturing,
            //        ProductName = string.Format("ProdName {0}", index),
            //        ProductStatus = Helper.ProductStatus.Defined,
            //        MaterialsQuantity = new ProductMaterialList()
            //    });
            //}

            //for (int index = 9; index < 11; index++)
            //{
            //    productCollection.Add(new ProductViewModel()
            //    {
            //        ProductCode = string.Format("ProdCode {0}", index),
            //        ProductType = Helper.EnumMaterialType.Manufacturing,
            //        ProductName = string.Format("ProdName {0}", index),
            //        ProductStatus = Helper.ProductStatus.Inward_InProgress,
            //        MaterialsQuantity = new ProductMaterialList()
            //    });
            //}

            //for (int index = 11; index < 14; index++)
            //{
            //    productCollection.Add(new ProductViewModel()
            //    {
            //        ProductCode = string.Format("ProdCode {0}", index),
            //        ProductType = Helper.EnumMaterialType.Manufacturing,
            //        ProductName = string.Format("ProdName {0}", index),
            //        ProductStatus = Helper.ProductStatus.Outward_ToBeDeliverd,
            //        Consumer = string.Format("Consumer {0}", index),
            //        MaterialsQuantity = new ProductMaterialList()
            //    });
            //}

            //for (int index = 14; index < 18; index++)
            //{
            //    productCollection.Add(new ProductViewModel()
            //    {
            //        ProductCode = string.Format("ProdCode {0}", index),
            //        ProductType = Helper.EnumMaterialType.Manufacturing,
            //        ProductName = string.Format("ProdName {0}", index),
            //        ProductStatus = Helper.ProductStatus.Delivered,
            //        Consumer = string.Format("Consumer {0}", index),
            //        Purchaser = string.Format("Purchaser {0}", index),
            //        MaterialsQuantity = new ProductMaterialList()
            //    });
            //}

            //for (int index = 18; index < 22; index++)
            //{
            //    productCollection.Add(new ProductViewModel()
            //    {
            //        ProductCode = string.Format("ProdCode {0}", index),
            //        ProductType = Helper.EnumMaterialType.Trading,
            //        ProductName = string.Format("ProdName {0}", index),
            //        ProductStatus = Helper.ProductStatus.Delivered,
            //        Purchaser = string.Format("Purchaser {0}", index),
            //        MaterialsQuantity = new ProductMaterialList()
            //    });
            //}

            //return productCollection;
        }

        public static EnumMaterialType GetMaterialType(string type)
        {
            switch(type)
            {
                case "Trading" :
                    return EnumMaterialType.Trading;
                case "Manufacturing":
                    return EnumMaterialType.Manufacturing;
                default:
                    return EnumMaterialType.Trading;
            }
        }
    }
}
